# -*- coding: utf-8 -*-
# @Author  : AI Assistant
# @Time    : 2024
# @Function: 使用SQL文件导入MultiPolygon和Point几何数据到PostGIS数据库

import pandas as pd
import geopandas as gpd
from loguru import logger
from sqlalchemy import create_engine, inspect, text
import os
import json


def create_geometry_table(db_url, sql_file_path):
    """
    使用SQL文件创建几何数据表
    
    Args:
        db_url: 数据库连接字符串
        sql_file_path: SQL文件路径
    """
    engine = create_engine(db_url)
    try:
        # 读取SQL文件内容
        with open(sql_file_path, 'r', encoding='utf-8') as f:
            sql_content = f.read()
        
        # 从SQL内容中提取表名
        import re
        table_match = re.search(r'CREATE\s+TABLE\s+IF\s+NOT\s+EXISTS\s+(\w+)', sql_content, re.IGNORECASE)
        if not table_match:
            table_match = re.search(r'CREATE\s+TABLE\s+(\w+)', sql_content, re.IGNORECASE)
        
        table_name = table_match.group(1) if table_match else "unknown_table"
        
        # 检查表是否已存在
        inspector = inspect(engine)
        if table_name in inspector.get_table_names():
            logger.info(f"表 '{table_name}' 已存在，跳过创建")
            return True
        
        with engine.connect() as conn:
            logger.info(f"创建表 '{table_name}' 使用SQL文件: {sql_file_path}")
            conn.execute(text(sql_content))
            conn.commit()
            logger.info(f"表 '{table_name}' 创建成功")
            return True
            
    except Exception as e:
        logger.error(f"创建表失败: {e}")
        return False
    finally:
        engine.dispose()

if __name__ == "__main__":
    # 示例用法
    db_url = "postgresql://postgres:water@10.48.0.85:5432/hydro_pulse"
    
    create_geometry_table(db_url, 'sql/geometry_multipolygons.sql')
    create_geometry_table(db_url, 'sql/geometry_points.sql')
